<?php
/**
 * Created by PhpStorm.
 * User: nightelf
 * Date: 2020/1/11
 * Time: 11:31
 */

namespace App\Http\Model;


use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class HandymanAccountsModel extends Model
{
    const TABLE = 'handyman_accounts';

    /**
     * @param array $data
     * @return mixed
     */
    public function getAccountsList(array $data)
    {
        return DB::table(self::TABLE)
            ->where(function($query)use ($data){
                if (!empty($data['employeeId'])){
                    $query->where('employeeId',$data['employeeId']);
                }
                if (!empty($data['startTime'])){
                    $query->where('separateTime','>=',$data['startTime']);
                }
                if (!empty($data['endTime'])){
                    $query->where('separateTime','<=',$data['endTime']);
                }
            })
            ->get()->toArray();
    }

    /**
     * @param array $data
     * @return mixed
     */
    public function getAccountsListWithInfo(array $data)
    {
        return DB::table(self::TABLE)
            ->leftJoin('project', 'project.id', '=', self::TABLE . '.projectId')
            ->leftJoin('profession as p', 'p.id', '=', self::TABLE . '.professionId')
            ->leftJoin('project_area as pa', 'pa.id', '=', self::TABLE . '.areaId')
            ->leftJoin('project_section as ps', 'ps.id', '=', self::TABLE . '.sectionId')
            ->leftJoin('assignment as a', 'a.id', '=', self::TABLE . '.assignmentId')
            ->leftJoin('admin','admin.id','=',self::TABLE . '.adminId')
            ->where(function($query)use ($data){
                if (!empty($data['employeeId'])){
                    $query->where('employeeId',$data['employeeId']);
                }
                if (!empty($data['startTime'])){
                    $query->where('separateTime','>=',$data['startTime']);
                }
                if (!empty($data['endTime'])){
                    $query->where('separateTime','<=',$data['endTime']);
                }
            })
            ->orderBy('separateTime')
            ->select(self::TABLE . '.*', 'p.name as professionName', 'pa.name as areaName', 'ps.name as sectionName', 'a.name as assignmentName', 'project.name as projectName','admin.name as adminName')
            ->get()->toArray();
    }

    /**
     * @param array $data
     * @return array
     */
    public function info(array $data)
    {
        $result = DB::table(self::TABLE)->where($data)->first();
        return empty($result) ? [] : get_object_vars($result);
    }

    /**
     * @param array $data
     * @return mixed
     */
    public function getSum(array $data)
    {
        return DB::table(self::TABLE)
            ->where(function($query)use($data){
                if (!empty($data['projectId'])){
                    $query->where('projectId',$data['projectId']);
                }
                if (!empty($data['areaId'])){
                    $query->where('areaId',$data['areaId']);
                }
                if (!empty($data['sectionId'])){
                    $query->where('sectionId',$data['sectionId']);
                }
                if (!empty($data['professionId'])){
                    $query->where('professionId',$data['professionId']);
                }
                if (!empty($data['startTime'])){
                    $query->where('separateTime','>=',$data['startTime']);
                }
                if (!empty($data['endTime'])){
                    $query->where('separateTime','<=',$data['endTime']);
                }
                if (!empty($data['employeeId'])){
                    $query->where('employeeId',$data['employeeId']);
                }
                if (isset($data['status'])){
                    if (!is_array($data['status'])){
                        $query->where('status',$data['status']);
                    }else{
                        $query->whereIn('status',$data['status']);
                    }
                }
            })->sum('account');
    }

    /**
     * @param array $data
     * @return mixed
     */
    public function insert(array $data)
    {
        return DB::table(self::TABLE)->insertGetId($data);
    }

    /**
     * @param array $param
     * @return mixed
     */
    public function lists(array $param)
    {
        $result = DB::table(self::TABLE)
            ->leftJoin('employee as e', 'e.id', '=', self::TABLE . '.employeeId')
            ->leftJoin('project', 'project.id', '=', self::TABLE . '.projectId')
            ->leftJoin('profession as p', 'p.id', '=', self::TABLE . '.professionId')
            ->leftJoin('project_area as pa', 'pa.id', '=', self::TABLE . '.areaId')
            ->leftJoin('project_section as ps', 'ps.id', '=', self::TABLE . '.sectionId')
            ->leftJoin('assignment as a', 'a.id', '=', self::TABLE . '.assignmentId')
            ->leftJoin('admin','admin.id','=',self::TABLE . '.adminId')
            ->where(function ($query) use ($param) {
                if (isset($param['projectId']) && $param['projectId'] != 0) {
                    $query->where(self::TABLE . '.projectId', $param['projectId']);
                }
                if (!empty($param['sectionId'])) {
                    $query->where(self::TABLE . '.sectionId', $param['sectionId']);
                }
                if (!empty($param['professionId'])) {
                    $query->where(self::TABLE . '.professionId', $param['professionId']);
                }
                if (!empty($param['employeeId'])) {
                    $query->where(self::TABLE . '.employeeId', $param['employeeId']);
                }
                if (!empty($param['startTime']) && !empty($param['endTime'])){
                    $query->whereBetween('separateTime',[$param['startTime'],$param['endTime']]);
                }
                if (!empty($param['separateTime'])){
                    $query->where('separateTime',$param['separateTime']);
                }
                if (isset($param['search']) && $param['search'] != '') {
                    $query->where(function ($query1) use ($param) {
                        $query1->where('e.name', 'like', '%' . $param['search'] . '%')->orWhere('e.jobNumber', 'like', '%' . $param['search'] . '%');
                    });
                }
            })
            ->orderBy(self::TABLE . '.separateTime', 'des');
        if (!empty($param['length']) && !empty($param['page'])){
            $limit = config('yucheng.limit');
            $start = 0;
            if (!empty($param['length'])) {
                $limit = $param['length'];
            }
            if (!empty($param['page'])) {
                $start = ($param['page'] - 1) * $limit;
            }
            $result = $result->offset($start)->limit($limit);
        }

            return $result->select(self::TABLE . '.*', 'e.name as employeeName', 'e.jobNumber', 'p.name as professionName', 'pa.name as areaName', 'ps.name as sectionName', 'a.name as assignmentName', 'project.name as projectName','admin.name as adminName')
            ->get()->toArray();
    }

    /**
     * @param array $param
     * @return mixed
     */
    public function countLists(array $param)
    {
        return DB::table(self::TABLE)
            ->leftJoin('employee as e', 'e.id', '=', self::TABLE . '.employeeId')
            ->leftJoin('project', 'project.id', '=', self::TABLE . '.projectId')
            ->leftJoin('profession as p', 'p.id', '=', self::TABLE . '.professionId')
            ->leftJoin('project_area as pa', 'pa.id', '=', self::TABLE . '.areaId')
            ->leftJoin('project_section as ps', 'ps.id', '=', self::TABLE . '.sectionId')
            ->leftJoin('assignment as a', 'a.id', '=', self::TABLE . '.assignmentId')
            ->where(function ($query) use ($param) {
                if (isset($param['projectId']) && $param['projectId'] != 0) {
                    $query->where(self::TABLE . '.projectId', $param['projectId']);
                }
                if (isset($param['sectionId']) && $param['sectionId'] != 0) {
                    $query->where(self::TABLE . '.sectionId', $param['sectionId']);
                }
                if (!empty($param['startTime']) && !empty($param['endTime'])){
                    $query->whereBetween('separateTime',[$param['startTime'],$param['endTime']]);
                }
                if (isset($param['search']) && $param['search'] != '') {
                    $query->where(function ($query1) use ($param) {
                        $query1->where('e.name', 'like', '%' . $param['search'] . '%')->orWhere('e.jobNumber', 'like', '%' . $param['search'] . '%');
                    });
                }
            })
            ->count();
    }

    /**
     * 月历形式的杂工记录表
     * @param array $param
     * @return mixed
     * @throws \Exception
     */
    public function lists2(array $param){
        $yearInfo = (new YearModel())->findYear(date('Y-m-d H:i:s', strtotime($param['month'])));
        $employeeLists = DB::table(self::TABLE)
            ->leftJoin('employee as e', 'e.id', '=', self::TABLE . '.employeeId')
            ->leftJoin('project', 'project.id', '=', self::TABLE . '.projectId')
            ->leftJoin('profession as p', 'p.id', '=', 'e.professionId')
            ->where(function ($query) use ($param){
                if (!empty($param['projectId'])){
                    $query->where(self::TABLE.'.projectId',$param['projectId']);
                }
            })
//            ->where('e.entryTime','>=',$yearInfo['startTime'])
//            ->where('e.entryTime','<=',$yearInfo['endTime'])
            ->orderBy('e.status','asc')
            ->groupBy('employeeId')
            ->select('employeeId',self::TABLE.'.status','e.name as employeeName', 'e.jobNumber','p.name as professionName','project.name as projectName','e.status as employeeStatus')
            ->get()->toArray();

        //获取月杂工情况，不传月份则默认为当前月份
        $startTime = (new \DateTime($param['month']))->format('Y-m-01');
        $endTime = (new \DateTime($param['month']))->format('Y-m-t');
        foreach ($employeeLists as $key=> $e) {
            $dayList = $this->datePeriod($startTime, $endTime, '1 day', 'j');
            $separate = DB::table(self::TABLE)
                ->where('employeeId',$e->employeeId)
                ->where(function ($query) use ($param){
                    if (!empty($param['projectId'])){
                        $query->where(self::TABLE.'.projectId',$param['projectId']);
                    }
                })
                ->whereBetween('separateTime',[$startTime,$endTime])
                ->groupBy('separateTime')
                ->select('separateTime',DB::raw('SUM(account) as accounts'))
                ->get()->toArray();
            if(empty($separate)){
                unset($employeeLists[$key]);
            }else{
                foreach ($separate as $a) {
                    $d = (new \DateTime($a->separateTime))->format('j');
                    $dayList[$d] = $a->accounts;
                }
                $employeeLists[$key]->accounts = $dayList;
            }
        }
        return array_values($employeeLists);
    }

    /**
     * @param array $data
     * @return mixed
     */
    public function delete(array $data)
    {
        return DB::table(self::TABLE)->where($data)->delete();
    }

    public function update(array $where, array $data)
    {
        return DB::table(self::TABLE)
            ->where(function($query)use($where){
                if (!empty($where['id'])){
                    $query->where('id',$where['id']);
                }
                if (!empty($where['employeeId'])){
                    $query->where('employeeId',$where['employeeId']);
                }
                if (!empty($where['professionId'])){
                    $query->where('professionId',$where['professionId']);
                }
                if (!empty($where['sectionId'])){
                    $query->where('sectionId',$where['sectionId']);
                }
                if (!empty($where['areaId'])){
                    $query->where('areaId',$where['areaId']);
                }
                if (!empty($where['projectId'])){
                    $query->where('projectId',$where['projectId']);
                }
                if (!empty($where['startTime']) && !empty($where['endTime'])){
                    $query->whereBetween('separateTime',[$where['startTime'],$where['endTime']]);
                }
                if (!empty($where['status'])){
                    $query->where('status',$where['status']);
                }
                if (!empty($where['adminId'])){
                    $query->where('adminId',$where['adminId']);
                }
            })
            ->update($data);
    }

    public function export(array $data)
    {
        $result = DB::table(self::TABLE)
            ->leftJoin('employee as e', 'e.id', '=', self::TABLE . '.employeeId')
            ->leftJoin('assignment as a', 'a.id', '=', self::TABLE . '.assignmentId')
            ->leftJoin('project_area as pa', 'pa.id', '=', self::TABLE . '.areaId')
            ->leftJoin('project_section as ps', 'ps.id', '=', self::TABLE . '.sectionId')
            ->where(function ($query)use($data){
                if (!empty($data['startTime'])){
                    $query->where('separateTime', '>=', $data['startTime']);
                }
                if (!empty($data['endTime'])){
                    $query->where('separateTime', '<=', $data['endTime']);
                }
            })
            ->where('employeeId', $data['employeeId'])
            ->where(self::TABLE.'.projectId',$data['projectId'])
            ->select(DB::raw("e.name,e.jobNumber,date_format( separateTime, '%Y-%m-%d' ) AS separateTime,sum(account) as accounts,group_concat(CONCAT_WS(',',pa.name,ps.name,a.name,assignmentDetail,CONCAT('',account,'元')) SEPARATOR '|') as work"))
            ->groupBy('separateTime')
            ->orderBy('separateTime', 'desc')
            ->get()->toArray();
        return $result;
    }

    /**
     * 在一个项目内筛选，某一段时间内有多少工人记录过杂工账
     * @param array $data
     * @return mixed
     */
    public function getHandyman(array $data)
    {
        $result = DB::table(self::TABLE)
            ->leftJoin('employee as e', 'e.id', '=', self::TABLE . '.employeeId')
            ->where(self::TABLE.'.projectId',$data['projectId'])
            ->where(function ($query)use($data){
                if (isset($data['search']) && $data['search'] != '') {
                    $query->where('e.name', 'like', '%' . $data['search'] . '%')->orWhere('e.jobNumber', 'like', '%' . $data['search'] . '%');
                }
                if (!empty($data['startTime'])){
                    $query->where('separateTime', '>=', $data['startTime']);
                }
                if (!empty($data['endTime'])){
                    $query->where('separateTime', '<=', $data['endTime']);
                }
            })
            ->distinct('employeeId')
            ->select('employeeId')
            ->get()->toArray();
        return $result;
    }

    /**
     * 筛选在某项目中的某工人，一段时间内，都在哪些项目记录过杂工账
     * @param array $data
     * @return mixed
     */
    public function getHandymanProjects(array $data){
        $result = DB::table(self::TABLE)
            ->leftJoin('employee as e', 'e.id', '=', self::TABLE . '.employeeId')
            ->leftJoin('project', 'project.id', '=', self::TABLE . '.projectId')
            ->where(function($query)use($data){
                if (!empty($data['startTime']) && !empty($data['endTime'])){
                    $query->whereBetween('separateTime',[$data['startTime'],$data['endTime']]);
                }
                if (isset($data['search']) && $data['search'] != '') {
                    $query->where(function ($query1) use ($data) {
                        $query1->where('e.name', 'like', '%' . $data['search'] . '%')->orWhere('e.jobNumber', 'like', '%' . $data['search'] . '%');
                    });
                }
            })
            ->distinct(self::TABLE.'.projectId')
            ->select(self::TABLE.'.projectId','project.name as projectName')
            ->get()->toArray();
        return $result;
    }
}